package com.rjyb.student_management_platform.dao;

import com.rjyb.student_management_platform.pojo.Class;
import com.rjyb.student_management_platform.pojo.*;
import com.rjyb.student_management_platform.unit.common.Page;
import com.rjyb.student_management_platform.unit.common.Pager;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;

public interface AdminInOpDao {
    //1.登入
    @Select("select * from admininop where opName = #{admininop.opName} and opPwd = #{admininop.opPwd}")
    Admininop toLogin(@Param("admininop") Admininop admininop);

    //2.询所有学生
    @Select("select  * from student LIMIT #{pager.beginIndex},#{pager.pageSize}")
    List<Student> seleallstudent(@Param("pager") Page page);

    //3.查询所有班级
    @Select("select * from class")
    List<Class> seleallclass();

    //4.查询所有老师
    @Select("select * from teacher where type=2")
    List<Teacher> seleallteacher();


    //5.条件查询学生条件1
    @Select("select * from class_teacher ct,class c,class_student cs,student s where ct.tid=#{tid} and c.cid=ct.cid and ct.cid=cs.cid and cs.sid=s.sid and s.name like concat ('%',#{name},'%')")
    List<Student> selestudet1(long tid, String name);

    //6.条件查询学生条件2
    @Select("select * from class_student cs,student s where cs.cid=#{cid} and s.sid=cs.sid  and s.name like concat ('%',#{name},'%')")
    List<Student> selestudet2(long cid, String name);

    //7.条件查询学生条件3
    @Select("select s.sid, s.name, s.password, s.studentId, s.sex, s.age, s.birthday, s.phone, s.idCard, s.address, s.nativePlace, s.nation from class_teacher ct,class_student cs,student s where ct.cid=#{cid} and cs.cid=#{cid} and ct.tid=#{tid} and cs.sid=s.sid and s.name like concat ('%',#{name},'%')")
    List<Student> selestudet3(long cid, long tid, String name);

    //8.条件查询学生条件4
    @Select("select * from student s where s.name like concat ('%',#{name},'%')")
    List<Student> selestudet4(String name);
    //9.查询总学生数量
    @Select("select count(*) from student")
    int selpagenum();


    //10.查询班级
    @Select("select * from class")
    List<Class> selectClass();
    @Select("select * from class  limit #{pager.beginIndex},#{pager.pageSize}")
    List<Class> selectClassTT(@Param("pager") Pager pager);
    //查询班级的分页查询
    @Select("select count(*) from class")
    int selectClassCT();

    //11.查询课程
    @Select("select * from booksubject")
    List<Booksubject> selectBooksubject();
    @Select("select * from booksubject   limit #{pager.beginIndex},#{pager.pageSize}")
    List<Booksubject> selectBooksubjectT(@Param("pager") Pager pager);
    //查询课程分页查询
    @Select("select count(*) from booksubject")
    int selectBooksubjectC();

    //12.添加学生名字学号手机号
    @Insert("insert into student values(null,#{name},'未添加',#{studentId},'未添加','未添加',#{birthday},#{phone},'未添加','未添加','未添加','未添加')")
    @Options(useGeneratedKeys = true, keyProperty = "sid", keyColumn = "sid")
    void AddStudentNameClass(Student student);

    //13.添加学生班级
    @Insert("insert into class_student values(null,#{cid},#{student.sid})")
    void AddStudentClass(@Param("student") Student student, @Param("cid") String cid);

    //14.添加老师名字工号和所教
    @Insert("insert into teacher values(null,#{name},'未添加',#{jobNumber},#{type},#{bid})")
    @Options(useGeneratedKeys = true, keyProperty = "tid", keyColumn = "tid")
    void AddTeacherBooksubject(Teacher teacher);

    //15.添加老师班级
    @Insert("insert into class_teacher values(null,#{cid},#{teacher.tid})")
    void AddTeacherClass(@Param("cid") String cid, @Param("teacher") Teacher teacher);

    //16.添加班级
    @Insert("insert into class values(null,#{className})")
    void AddClass(Class c);


    //17.查询所有老师
    @Select("SELECT * FROM teacher t,class_teacher ct,class c where t.tid=ct.tid and ct.cid=c.cid  ORDER BY t.tid ASC limit #{beginIndex},#{pageSize}")
    List<HashMap> selectAllTeacher(Pager pager);
    //18.
    @Select("SELECT count(*) FROM teacher t,class_teacher ct,class c where t.tid=ct.tid and ct.cid=c.cid")
    int selectAllTeacherC();

    //19.查询所有班级
    @Select("SELECT * FROM class ")
    List<HashMap> selectAllClass(Pager pager);
    //20.
    @Select("SELECT count(*) FROM class")
    int selectAllClassC();

    //21.通过班级查老师
    @Select("SELECT * FROM teacher t,class_teacher ct,class c where c.className=#{className} and c.cid=ct.cid and ct.tid=t.tid ORDER BY t.tid ASC limit #{pager.beginIndex},#{pager.pageSize}")
    List<HashMap> selectTOfName(@Param("className") String className, @Param("pager") Pager pager);
    //22.
    @Select("SELECT count(*) FROM teacher t,class_teacher ct,class c where c.className=#{className} and c.cid=ct.cid and ct.tid=t.tid")
    int selectTOfNameC(String className);

    //23.通过老师名字来搜索老师
    @Select("SELECT * FROM teacher t,class_teacher ct,class c where t.tid=ct.tid and ct.cid=c.cid and t.name like concat ('%',#{teacherName},'%') ORDER BY t.tid ASC limit #{pager.beginIndex},#{pager.pageSize}")
    List<HashMap> selectTOfTName(@Param("teacherName") String teacherName, @Param("pager") Pager pager);
    //24.
    @Select("SELECT count(*) FROM teacher t,class_teacher ct,class c where t.tid=ct.tid and ct.cid=c.cid and t.name like concat ('%',#{teacherName},'%')")
    int selectTOfTNameC(String teacherName);

    //25.查询所有作业
    @Select("select * from jobtable j,class c,teacher t where j.cid=c.cid and j.tid=t.tid limit #{pager.beginIndex},#{pager.pageSize}")
    List<HashMap> selectHomeWorkA(@Param("pager") Pager pager);
    //26.
    @Select("select count(*) from jobtable j,class c,teacher t where j.cid=c.cid and j.tid=t.tid")
    Integer selectHomeWorkC();

    //27.查询作业关联班级
    @Select("SELECT * FROM jobtable jt, class c,teacher t,class_teacher ct WHERE  jt.jid=#{jid} AND t.tid=jt.tid AND ct.cid=jt.cid AND c.cid=jt.cid AND t.tid=ct.tid ")
    List<HashMap> selectWorkClassname(long jid);

    //28.查询作业完成情况
    //查询已完成的学生
    @Select("select * from answer_sheet a ,student s ,jobtable jt,class_student cs  where cs.cid=#{cid} and a.jid=#{jid} and a.sid=cs.sid and s.sid=cs.sid and jt.jid=#{jid}")
    List<HashMap> selectWorkComplete(@Param("jid") long jid, @Param("cid") long cid);

    //29.查看老师
    @Select("select * from teacher")
    List<Teacher> seleteacher();

    //30.查看老师带的班级有没有重复的
    @Select("select * from class_teacher where tid=#{tid} and cid=#{cid}")
    Class_Teacher selectClass_Teacher(Class_Teacher class_teacher);

    //31.查看学生有没有注册过
    @Select("select * from student where studentId=#{studentId} ")
    Student ifselectStudent(Student student);

    //32.查看老师有没有注册过
    @Select("select * from teacher where jobNumber=#{jobNumber}")
    Teacher ifselectTeacher(Teacher teacher);

    //33.查询考勤
    @Select("select * from attendance a,class c,student s where  a.tid=#{tid} and a.cid=#{cid} and c.cid=#{cid} and s.sid=a.sid limit #{pager.beginIndex},#{pager.pageSize}")
    List<HashMap> selectAllAttendancd(@Param("cid") long cid, @Param("pager") Pager page, @Param("tid") long tid);
    //34.
    @Select("select count(*) from attendance a,class c,student s where a.tid=#{tid} and a.cid=#{cid} and c.cid=#{cid} and s.sid=a.sid limit #{pager.beginIndex},#{pager.pageSize}")
    int selectAllAttendancdC(@Param("cid") long cid, @Param("pager") Pager page, @Param("tid") long tid);

    //35.按照第几节课查考勤
    @Select("select * from attendance a,class c,student s where a.cid=#{cid} and a.atDate=#{atDate} and a.class_section=#{classsection} and c.cid=a.cid and s.sid=a.sid and a.tid=#{tid}")
    List<HashMap> selectClassSection(@Param("classsection") Integer classsection, @Param("cid") long cid, @Param("atDate") String atDate, @Param("tid") long tid);

    //36.查老师和带的班
    @Select("SELECT * FROM teacher t,class_teacher ct,class c WHERE t.tid=ct.tid AND ct.cid=c.cid limit #{pager.beginIndex},#{pager.pageSize} ")
    List<HashMap> selectClassT(@Param("pager") Pager pager);
    //37.查老师带的班分页
    @Select("SELECT count(*) FROM teacher t,class_teacher ct,class c WHERE t.tid=ct.tid AND ct.cid=c.cid; ")
    Integer selectClassC();

    //38.通过带的班查考勤
    @Select("select * from class c,teacher t ,class_teacher ct where c.cid=#{cid} and c.cid=ct.cid and ct.tid=t.tid ")
    List<HashMap> selectCOfName(long cid);

    //39.通过名字查考勤
    @Select("select * from  class c,teacher t ,class_teacher ct where ct.tid=t.tid and ct.cid=c.cid and t.name like concat ('%',#{teacherName},'%') ")
    List<HashMap> selectCOfAtt(String teacherName);

    //40.作业筛选
    @Select("select * from jobtable j,class c,teacher t where j.cid=#{cid} and j.cid=c.cid and j.tid=t.tid")
    List<HashMap> selectAllWorkPlus(long cid);
    //作业模糊查询
    @Select("select * from jobtable j,class c,teacher t where j.cid=c.cid and j.tid=t.tid  and j.jobName like concat ('%',#{hoName},'%')")
    List<HashMap>  selectWorkN(@Param("hoName") String hoName);


    //查看课程有没有给注册过
    @Select("select * from booksubject where subjectName=#{subjectName}")
    Booksubject ViewCourseRegistrations(Booksubject booksubject);

    //添加课程
    @Insert("INSERT INTO booksubject values(null,#{subjectName})")
    void AddCourse(Booksubject booksubject);

    //查看添加班级重复
    @Select("select * from Class where className=#{className}")
    Class ClassQuery(Class c);
}
